package com.panopset.rdb;

import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.h2.tools.Server;

import com.google.inject.Inject;
import com.panopset.Util;

/**
 * Database store, which defines the database name and connection details.
 *
 * @author Karl Dinwiddie.
 *
 */
public final class Store {

    /**
     * Usually &lt;app.getUserBaseDirectory()&gt;.
     */
    private final String baseDir;

    /**
     * Default store.
     */
    public static final String DEFAULT_STORE = "store";

    /**
     * Default base.
     */
    public static final String DFT_BASE = ".com.panopset";

    /**
     * @param storeName
     *            Store name. Default is <b>store</b>.
     */
    @Inject
    public Store(final String storeName, final String baseDirectory) {
        baseDir = baseDirectory;
        store = storeName;
        try {
            Class.forName("org.h2.Driver");
        } catch (ClassNotFoundException e) {
            Util.log(e);
        }
    }

    /**
     * @param table
     *            Table.
     * @param key
     *            Key.
     * @param values
     *            Value.
     */
    public void put(final Table table, final String key,

    final String... values) {
        StringWriter sw = new StringWriter();
        Object[] currDta = get(table, key);
        if (currDta == null || currDta.length == 0) {
            prepareTable(table);
            sw = new StringWriter();
            sw.append("insert into ");
            sw.append(table.getSchemaDotName());
            sw.append(" (");
            sw.append(table.getPrimaryKey().getName());
            int i = 0;
            for (Field f : table.getFields()) {
                sw.append(",");
                sw.append(f.getName());
            }
            sw.append(") values ('");
            sw.append(key);
            i = 0;
            for (Field f : table.getFields()) {
                sw.append("',");
                sw.append(f.getQuoteIfNecessary());
                sw.append(values[i++]);
                sw.append(f.getQuoteIfNecessary());
            }
            sw.append(")");
            exec(sw.toString());
        } else {
            sw = new StringWriter();
            sw.append("update ");
            sw.append(table.getSchemaDotName());
            sw.append(" set ");
            int i = 0;
            for (Field f : table.getFields()) {
                if (i > 0) {
                    sw.append(",");
                }
                sw.append(f.getName());
                sw.append(" = ");
                sw.append(f.getQuoteIfNecessary());
                sw.append(values[i++]);
                sw.append(f.getQuoteIfNecessary());
            }
            sw.append(" where ");
            sw.append(table.getPrimaryKey().getName());
            sw.append(" = ");
            sw.append(table.getPrimaryKey().getQuoteIfNecessary());
            sw.append(key);
            sw.append(table.getPrimaryKey().getQuoteIfNecessary());
            exec(sw.toString());
        }
    }

    /**
     * Create the table and it's schema if it doesn't exit.
     *
     * @param table
     *            Table.
     */
    private void prepareTable(final Table table) {
        exec("create schema if not exists " + table.getSchema());
        StringWriter sw = new StringWriter();
        sw.append("create table if not exists ");
        sw.append(table.getSchemaDotName());
        sw.append(" (");
        sw.append(table.getPrimaryKey().getDefinition());
        sw.append(" primary key");
        for (Field f : table.getFields()) {
            sw.append(", ");
            sw.append(f.getDefinition());
        }
        sw.append(")");
        exec(sw.toString());
    }

    /**
     * @param sql
     *            Simple statement to execute.
     */
    private void exec(final String sql) {
        Connection conn = null;
        try {
            conn = createConnection();
            exec(conn, sql);
        } catch (Exception e) {
            throw new RuntimeException(sql, e);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Util.log(e);
                }
            }
        }
    }

    /**
     *
     * @param conn
     *            Connection.
     * @param sql
     *            SQL.
     */
    private void exec(final Connection conn, final String sql) {
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            exec(ps);
            Util.logv(sql);
            conn.commit();
        } catch (Exception ex) {
            Util.log(sql);
            Util.log(ex);
            throw (new RuntimeException(ex));
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    Util.log(e);
                }
            }
        }
    }

    /**
     * Execute a prepared statement.
     *
     * @param ps
     *            Prepared statement.
     */
    private void exec(final PreparedStatement ps) {
        try {
            Connection conn = ps.getConnection();
            ps.execute();
            Util.logv(ps.toString());
            conn.commit();
        } catch (Exception ex) {
            Util.log(ps.toString());
            Util.log(ex);
        }
    }

    /**
     * @param table
     *            Table.
     * @param key
     *            Key.
     * @return Value for key, or null if row matching key not found.
     */
    public Object[] get(final Table table, final String key) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        StringWriter sw = new StringWriter();
        Object[] rtn = null;
        try {
            sw.append("select * from ");
            sw.append(table.getSchemaDotName());
            sw.append(" where ");
            sw.append(table.getPrimaryKey().getName());
            sw.append(" = ?");
            conn = createConnection();
            stmt = conn.prepareStatement(sw.toString());
            stmt.setString(1, key);
            Util.logv(stmt.toString());
            stmt.execute();
            rs = stmt.getResultSet();
            if (rs.next()) {
                rtn = new Object[table.getFields().size()];
                int i = 0;
                for (Field f : table.getFields()) {
                    rtn[i++] = f.getValueFromResultSet(rs);
                }
            }
            return rtn;
        } catch (Exception ex) {
            return null;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    Util.log(e);
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    Util.log(e);
                }
            }
            if (conn != null) {
                try {
                    conn.commit();
                } catch (SQLException e) {
                    Util.log(e);
                }
                try {
                    conn.close();
                } catch (SQLException e) {
                    Util.log(e);
                }
            }
        }
    }

    /**
     * Create an H2 database connection.
     *
     * @return Connection.
     * @throws Exception
     *             exception.
     */
    private Connection createConnection() throws Exception {
        return DriverManager.getConnection(getUrl(), "foo", "bar");
    }

    /**
     * Server thread.
     */
    private static Thread serverThread;

    /**
     * Start server.
     */
    public synchronized void startServer() {
        if (serverThread != null) {
            return;
        }
        serverThread = new Thread(new Runnable() {
            @Override
            public void run() {
                try {
                    Server.startWebServer(createConnection());
                    serverThread = null;
                } catch (Exception e) {
                    Util.log(e);
                }
            }

        });
        serverThread.start();
    }

    /**
     * Connection URL.
     */
    private String url;

    /**
     * @return Connection URL.
     */
    public String getUrl() {
        if (url == null) {
            StringWriter sw = new StringWriter();
            sw.append(getBase());
            sw.append(getStore());
            sw.append(";FILE_LOCK=NO");
            url = sw.toString();
        }
        return url;
    }

    /**
     * @return store.
     */
    public String getStore() {
        return store;
    }

    /**
     * @return base.
     */
    private String getBase() {
        if (base == null) {
            StringWriter sw = new StringWriter();
            sw.append("jdbc:h2:~/");
            sw.append(baseDir);
            sw.append("/");
            base = sw.toString();
        }

        return base;
    }

    /**
     * Base url. jdbc:h2:~/&lt;app.getUserBaseDirectory()&gt;/.
     */
    private String base;

    /**
     * Name.
     */
    private final String store;
}
